CSCI 0052. Introduction to SQL
Units: 3
Prerequisite: Completion of CSCI 10 with grade of "C" or better
Hours: 72 (54 lecture, 18 laboratory)
Survey of SQL (Structured Query Language). Includes database models, database design, table and view definition, transaction and data manipulation, queries and reports, data integrity, stored procedures, triggers, recovery and security. Hands-on experience using a popular SQL database. (CSU)
CSCI 0052 - Introduction to SQL
http://catalog.sierracollege.edu/course-outlines/csci-0052/
Catalog Description DESCRIPTION IS HERE: Prerequisite: Completion of CSCI 10 with grade of "C" or better Hours: 72 (54 lecture, 18 laboratory) Description: Survey of SQL (Structured Query Language). Includes database models, database design, table and view definition, transaction and data manipulation, queries and reports, data integrity, stored procedures, triggers, recovery and security. Hands-on experience using a popular SQL database. (CSU) Units 3 Lecture-Discussion 54 Laboratory 18 By Arrangement Contact Hours 72 Outside of Class Hours Course Student Learning Outcomes Design a database schema based on the conceptual model. Explain the major components of a Database Management System (DBMS). Use DBMS software to create a database in an Entity Relationship Diagram. Query the data in a relational DBMS by creating SQL commands. Course Content Outline I. Overview of Databases A. Database Models B. Data Types C. Query Languages II. Database Design A. Entity Relationship Diagrams B. Schemas C. Cardinality D. Primary and Foreign Keys III. Structured Query Language (SQL) A. Syntax B. Basic Commands i. CREATE TABLE, DROP TABLE ii. SELECT, INSERT, UPDATE, DELETE iii. WHERE, ORDER BY, GROUP BY, HAVING, LIMIT C. Joins i. Inner, Outer, Left, Right ii. Cartesian Product D. Functions i. Built-in a. String manipulation b. Date formatting and calculations c. Operators ii. Aggregate Functions (MIN, MAX, AVG, COUNT, SUM) iii. Stored Procedures and Functions E. Constraints F. Triggers G. Views H. Transactions I. Subqueries i. Static ii. Correlated J. Access Rights IV. Optimization A. Normal Forms B. Indexes C. Query Optimization V. Backups VI. Security A. Permissions B. GRANT, REVOKE VI. Current topics A. Non-relational databases B. NoSQL Course Objectives Course Objectives Lecture Objectives: 1. Design a database consisting of two or more tables from a written description of requirements. The design should include appropriately-named entities and attributes and appropriate data types. 2. Express a database design in an Entity Relationship Diagram using industry-recognized symbologies. 3. Enforce data integrity by utilizing any or all of the following constraints: primary key, foreign key, not null, unique, and check. 4. Analyze a query for potential optimizations and re-express the query in a more optimal form. 5. Analyze the schema of an un-normalized database and transform it into equivalent 1st, 2nd, and 3rd Normal Forms. 6. Identify many-to-many relationships between tables in a database design and transform them into equivalent one-to-many relationships using resolution tables. 7. Analyze tables to determine where indexes would increase the efficiency of queries. Apply indexes to the appropriate tables and fields. Test the hypothesis by measuring the increase or decrease in efficiency. Laboratory Objectives: 1. Construct valid SQL commands to create tables corresponding to a written description or ERD. 2. Construct valid SQL commands to insert, modify, and delete data into/from the tables. 3. Construct valid SQL commands to populate tables from tab-delimited and comma-separated external data files. 4. Based on written descriptions of the data to be selected and the format and ordering of the output, construct valid SQL commands to select data from one or more tables using joins, nested subqueries, functions, and/or operators where necessary. 5. Construct valid SQL commands to create and use views of multiple tables. 6. Construct valid SQL commands to create and use stored procedures and functions. Methods of Evaluation Objective Examinations Problem Solving Examinations Projects Reading Assignments 1. Read the chapter about table joins. Summarize the SQL commands and options used to implement joins on a piece of paper to be used as reference during class discussion. 2. Two popular databases, PostgreSQL and MySQL, handle views differently. In particular, one of them supports updatable views and the other does not. Read the online documentation for both database systems and identify the one that does not support updatable views. Continue reading the documentation to discover how the built-in rules system can be modified to, in effect, support updatable views by overriding default behaviors. Be prepared to discuss in class. Writing, Problem Solving or Performance 1. Read the case study for a video library as given in the handout. Then do the following: a. Design a database that meets the criteria expressed in the case study. Draw an entity relationship diagram corresponding to your design. b. Create the tables from your design by writing the appropriate SQL commands. c. Populate the tables from the sample data given by the instructor. d. From the data, answer the following questions: * How many videos are currently being rented? * Which customers are renting videos? * Late fees are $2 per day. How much is owed to the video rental company from videos that are late? 2. You have been given a large spreadsheet which contains data in an un-normalized form. Using the normalization techniques discussed in class and from your readings, convert the flat (one table) spreadsheet into Third Normal Form (3NF) by drawing an ERD. Construct the appropriate SQL commands to implement your design on the computer. Other (Term projects, research papers, portfolios, etc.) Methods of Instruction Laboratory Lecture/Discussion Distance Learning Other materials and-or supplies required of students that contribute to the cost of the course.
Course Identification Numbering System (C-ID)
...COMP 122 CSCI 0012 COMP 132 CSCI 0013...0055 AJ 122 ADMJ 0052 AJ 124 ADMJ...
